package com.zz.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import com.zz.entity.Statistics;

public interface StatisticsDao extends JpaRepository<Statistics, Integer>,JpaSpecificationExecutor<Statistics> {
	
	
	/**
	 * 自定义查询
	 */
	//添加当天资源汇总
	@Transactional
	@Modifying
	@Query(value="insert into statistics(People_nums,Year,Month,day,date,Read_room_id,read_rooms_id) value(:#{#s.PeopleNums},:#{#s.Year},:#{#s.Month},:#{#s.day},:#{#s.date},:#{#s.ReadRoomId},:read_room_id)",nativeQuery=true)
	Integer addTimingStatistics(Statistics s,@Param("read_room_id")Integer id);
	
	//查询某个资源室某个时间段的人数总和
	@Query(value="select sum(people_nums) from statistics where read_rooms_id=?1 and date>?2 and date<?3",nativeQuery=true)
	Integer getPeoplenumsByDate(Integer id,String begin,String end);
	
	//查询所有资源室人数
	@Query(value="SELECT r.name,(CASE WHEN(s.sum is NULL or s.sum = '')THEN 0 ELSE s.sum end) sum FROM readrooms r LEFT JOIN (SELECT SUM(people_nums) sum,read_rooms_id id FROM statistics where date>?1 and date<?2 GROUP BY read_rooms_id) s ON r.Id = s.id",nativeQuery=true)
	List<Map<String, Integer>> getStatisticsPeopleNums(String begin,String end);
	
}
